using Avanzis.QueryFramework;
using NUnit.Framework;

namespace Avanzis.QueryBuilder.NUnit.MsSql2005
{  

    [TestFixture]
    public class MsSql2005SelectBuilderTest : MsSql2000.MsSql2000SelectBuilderTest
    {
        private Dialects previousDialect = Dialects.MSSql2005Dialect;

        [TestFixtureSetUp()]
        public override void Setup()
        {
            previousDialect = QueryFrameworkConfiguration.Dialect;
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2005Dialect;
            QueryProcessor.ResetProcessor();
        }


        [TestFixtureTearDown()]
        public override void TearDown()
        {
            QueryFrameworkConfiguration.Dialect = previousDialect;
        }

        [Test]        
        public override void SimpleOffset10()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";

            string result = "WITH [__pagedQuery__] AS (\n" +
                "SELECT ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP ASC) AS RowNumber,ArticleId,Name\n" +
                "FROM [Articles]\n" +
                ")\n" +
                "SELECT *\n" +
                "FROM [__pagedQuery__]\n" +
                "WHERE (RowNumber >= 11)\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]        
        public override void SimpleLimit20Offset10()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Limit = 20;
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";

            string result = "WITH [__pagedQuery__] AS (\n" +
                "SELECT ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP ASC) AS RowNumber,ArticleId,Name\n" +
                "FROM [Articles]\n" +
                ")\n" +
                "SELECT *\n" +
                "FROM [__pagedQuery__]\n" +
                "WHERE (RowNumber >= 11) AND (RowNumber <= 30)\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleLimit20Offset10WithOrderBy()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Limit = 20;
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";
            sb.OrderBy.Fields.Add("Name", OrderDirection.Asc);

            string result = "WITH [__pagedQuery__] AS (\n" +
                "SELECT ROW_NUMBER() OVER(ORDER BY Name ASC) AS RowNumber,ArticleId,Name\n" +
                "FROM [Articles]\n" +
                ")\n" +
                "SELECT *\n" +
                "FROM [__pagedQuery__]\n" +
                "WHERE (RowNumber >= 11) AND (RowNumber <= 30)\n";


            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public virtual void SimpleLimit20Offset10WithMultipleOrderBy()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Limit = 20;
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";
            sb.OrderBy.Fields.Add("Name", OrderDirection.Asc);
            sb.OrderBy.Fields.Add("ArticleId", OrderDirection.Desc);

            string result = "WITH [__pagedQuery__] AS (\n" +
                "SELECT ROW_NUMBER() OVER(ORDER BY Name ASC,ArticleId DESC) AS RowNumber,ArticleId,Name\n" +
                "FROM [Articles]\n" +
                ")\n" +
                "SELECT *\n" +
                "FROM [__pagedQuery__]\n" +
                "WHERE (RowNumber >= 11) AND (RowNumber <= 30)\n";
              

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }
    }
}
